﻿
CREATE PROCEDURE [dbo].[proc_AB_Ye_Create]
(
	@CompanyId INT,
	@Iid INT,
	@je FLOAT,
	@Person nVarchar(20),
	@Personid nVarchar(20),
	@PaymentTypeId INT,
	@PaymentTypeName NVarchar(200),
	@BankId INT,
	@BankName NVarchar(200),
	@InterfaceId NVarchar(200),
	@ZHJB1 INT 
)
AS
BEGIN
--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 Begin--
	Declare @nDays int,@nHour int,@nMinute int,@dToday DateTime
	Set @dToday=dbo.Get_SetDateTime()
--By 赵洪涛 2012-08-13 将16:30后的数据调整到第2天 End --

		BEGIN TRANSACTION--开始事务
		DECLARE @errorSun INT --定义错误计数器
		SET @errorSun=0 --没错为0

	declare @departmentId int
	Select @departmentId=Id From Department 
	  Where Id=(Select min(DepartmentId) from Employee where UserName =@Personid)

	DECLARE @zhjb INT  --接口账号还是预存账号(0 接口账号,>0 预存账号)
	Select @zhjb=syzh From AB_Interface WHERE id=@Iid
	If @zhjb Is Null
		Return -1	

	IF(@zhjb=0) --接口账号
		BEGIN
			--DECLARE @old_ye Varchar(50)
			--Set @old_ye=IsNull(Convert(Varchar(50),(Select ye From AB_list WHERE Iid=@Iid AND Convert(Varchar(10),UpTime,120)=Convert(Varchar(10),@dToday,120) AND AB_Type=0)),'')
			--当日该账号的余额
			Declare @old_ye Numeric(18,2)
			Select @old_ye=ye From AB_list WHERE Iid=@Iid And AB_Type=0 And Convert(varchar(10),UpTime,23)=Convert(varchar(10),@dToday,23)
			IF(@old_ye Is Null)	--当日没发生业务
				BEGIN
					--该账号最后一次的余额
					Set @old_ye=IsNull((Select TOP 1 ye From AB_list WHERE Iid=@Iid And AB_Type=0 ORDER BY UpTime desc),0)
					
					--插入支出记录(AB_Type=0,je=0,ye=old_ye+je,Items=0,rootId=1)
					INSERT INTO AB_list(CompanyId,Iid,AB_Type,je,ye,UpTime,Person,Items,rootId)
						VALUES (@CompanyId,@Iid,0,0,Convert(float,@old_ye)+Convert(float,@je),@dToday,@Person,0,1)
					
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
					
					--插入存款记录(AB_Type=1,je=je,ye=old_ye+je,rootId=1)
					INSERT INTO AB_list (CompanyId,Iid,AB_Type,je,ye,UpTime,Person,rootId)
					   VALUES (@CompanyId,@Iid,1,@je,Convert(float,@je)+Convert(float,@old_ye),@dToday,@Person,1)
				
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
				
				END
			ELSE	--已发生业务
				BEGIN
					--插入存款记录(AB_Type=1,je=je,ye=old_ye+je,rootId=1)
					INSERT INTO AB_list(CompanyId,Iid,AB_Type,je,ye,UpTime,Person,rootId)
						VALUES (@CompanyId,@Iid,1,@je,Convert(float,@je)+Convert(float,@old_ye),@dToday,@Person,1)
						
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
						
						
					--更改该账号余额(ye=old_ye+je)
					UPDATE AB_list Set ye=Convert(float,@je)+Convert(float,@old_ye) WHERE Iid=@Iid AND Convert(Varchar(10),UpTime,23)=Convert(Varchar(10),@dToday,23) AND AB_Type=0
					
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
				
				END	
		END
	ELSE	--预存账号
		BEGIN
--			DECLARE @UpTime1 datetime	
			DECLARE @UpTime2 datetime	
--			DECLARE @ye1 FLOAT
			DECLARE @ye2 FLOAT
--			DECLARE @id1 INT
			DECLARE @id2 INT
			
			--接口账号的id，最后业务日期，余额
--			Select TOP 1 @id1=id,@UpTime1=UpTime,@ye1=ye From AB_list WHERE Iid=@zhjb AND AB_Type=0 ORDER BY UpTime DESC
			--预存账号的id，最后业务日期，余额
			Select TOP 1 @id2=id,@UpTime2=UpTime,@ye2=ye From AB_list WHERE Iid=@Iid AND AB_Type=0 ORDER BY UpTime DESC

--			If(@ye1 Is NULL)
--				Set @ye1=0
			If(@ye2 Is NULL)
				Set @ye2=0

			If(Convert(varchar(10),@UpTime2,23)=Convert(varchar(10),@dToday,23))	--已发生业务
				BEGIN
					--插入存款记录(AB_Type=1,je=je,ye=ye2+je,rootId=2)
					INSERT INTO AB_list (CompanyId,Iid,AB_Type,je,ye,UpTime,Person,rootId,syzh)
							VALUES (@CompanyId,@Iid,1,@je,Convert(float,@je)+@ye2,@dToday,@Person,2,@zhjb)
							
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
							
					--更改该账号余额(ye=old_ye+je)
					UPDATE AB_list Set ye=@ye2+Convert(float,@je) WHERE id=@id2
					
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
					
				END
			ELSE	--当日没发生业务
				BEGIN
					--插入支出记录(AB_Type=0,je=0,ye=ye2+je,Items=0,rootId=1)
					INSERT INTO AB_list (CompanyId,Iid,AB_Type,je,ye,UpTime,Person,Items,rootId,syzh)
					VALUES (@CompanyId,@Iid,0,0,@ye2+Convert(float,@je),@dToday,@Person,0,2,@zhjb)
					
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
					
					--插入存款记录(AB_Type=1,je=je,ye=ye2+je,rootId=1)
					INSERT INTO AB_list (CompanyId,Iid,AB_Type,je,ye,UpTime,Person,rootId,syzh)
					VALUES (@CompanyId,@Iid,1,@je,Convert(float,@je)+@ye2,@dToday,@Person,2,@zhjb)
					
					if @@ROWCOUNT<1
					begin
						set @errorSun=@errorSun+1		
						goto e;
					end
				END			
		END
		
--存的是预存账号时走一笔支出
--	IF @ZHJB1=2
	If @zhjb>0
	BEGIN	
		INSERT INTO [Finance_Outlay]
           ([CreateDate]
           ,[TradeDate]
           ,[Summary]
           ,[CateOneId]
           ,[CateOneName]
           ,[CateTwoId]
           ,[CateTwoName]
           ,[SubjectId]
           ,[SubjectName]
           ,[PaymentTypeId]
           ,[PaymentTypeName]
           ,[BankId]
           ,[BankName]
           ,[OutlayReason]
           ,[OutlayPrice]
           ,[Agent]
           ,[AgentName]
           ,[PZPerson]
           ,[PZPersonName]
           ,[SHPerson]
           ,[SHPersonName]
           ,[BillNumber]
           ,[Description]
           ,[UserName]
           ,[EName]
           ,[CompanyId]
           ,[CompanyIdCur]
           ,[DepartmentIdCur])
		 VALUES
           (@dToday
           ,Convert(Varchar(10),@dToday,120)
           ,@InterfaceId
           ,19
           ,'成本类'
           ,20
           ,'直接成本'
           ,166
           ,'产品成本'
           ,@PaymentTypeId
           ,@PaymentTypeName
           ,@BankId
           ,@BankName
           ,@InterfaceId
           ,@je
           ,@Personid
           ,@Person
           ,''
           ,''
           ,''
           ,''
           ,''
           ,''
           ,@Personid
           ,@Person
           ,@CompanyId
           ,@CompanyId
           ,@departmentId)
           
           if @@ROWCOUNT<1
			begin
				set @errorSun=@errorSun+1		
				goto e;
			end
	END
	
	e:
    --PRINT @errorSun
	if @errorSun>0
	begin
		rollback TRANSACTION
	end
	else
	begin
		commit TRANSACTION
	END
END